package com.tust.dao;

import com.tust.domain.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class StudentDao {
    @Autowired
    JdbcTemplate jdbcTemplate;

    public int insertStudent(Student student) {
        return jdbcTemplate.update("INSERT INTO student (studentNum, studentName, studentSex, studentSdept, studentMajor, studentClassNum) VALUES (?, ?, ?, ?, ?, ?)",
                student.getStudentNum(), student.getStudentName(), student.getStudentSex(), student.getStudentSdept(), student.getStudentMajor(), student.getStudentClassNum());
    }

    public int deleteStudent(String studentNum) {
        return jdbcTemplate.update("DELETE FROM student WHERE studentNum=?", studentNum);
    }

    public int updateStudent(String oldStudentNum, Student student) {
        return jdbcTemplate.update("UPDATE student SET studentNum=?, studentName=?, studentSex=?, studentSdept=?, studentMajor=?, studentClassNum=? WHERE studentNum=?",
                student.getStudentNum(), student.getStudentName(), student.getStudentSex(), student.getStudentSdept(), student.getStudentMajor(), student.getStudentClassNum(), oldStudentNum);
    }

    public List<Student> selectStudentByStudentNum(String studentNum) {
        return jdbcTemplate.query("SELECT * FROM student WHERE studentNum=? ORDER BY studentNum",
                new BeanPropertyRowMapper<>(Student.class), studentNum);
    }

    public List<Student> selectStudentByStudentName(String studentName) {
        return jdbcTemplate.query("SELECT * FROM student WHERE studentName=? ORDER BY studentNum",
                new BeanPropertyRowMapper<>(Student.class), studentName);
    }

    public List<Student> selectStudentByStudentSex(String studentSex) {
        return jdbcTemplate.query("SELECT * FROM student WHERE studentSex=? ORDER BY studentNum",
                new BeanPropertyRowMapper<>(Student.class), studentSex);
    }

    public List<Student> selectStudentByStudentSdept(String studentSdept) {
        return jdbcTemplate.query("SELECT * FROM student WHERE studentSdept=? ORDER BY studentNum",
                new BeanPropertyRowMapper<>(Student.class), studentSdept);
    }

    public List<Student> selectStudentByStudentMajor(String studentMajor) {
        return jdbcTemplate.query("SELECT * FROM student WHERE studentMajor=? ORDER BY studentNum",
                new BeanPropertyRowMapper<>(Student.class), studentMajor);
    }

    public List<Student> selectStudentByStudentClassNum(String studentClassNum) {
        return jdbcTemplate.query("SELECT * FROM student WHERE studentClassNum=? ORDER BY studentNum",
                new BeanPropertyRowMapper<>(Student.class), studentClassNum);
    }

    public List<Student> selectStudentAll() {
        return jdbcTemplate.query("SELECT * FROM student ORDER BY studentNum",
                new BeanPropertyRowMapper<>(Student.class));
    }
}
